<?php
//require_once('../../common/_init.php');
//require_once('../../common/_function.php');
//require_once(CLASS_PATH.GENERAL_PATH."/__autoload.php");

// $con     = objectFactory::create('P79DAO');

/**
$qry = 'PREPARE statement FROM "SELECT lp.pSerial, lp.pType, lp.pMaker, lp.pName, lp.pComment FROM list_printer AS lp, haystack_printer AS hp WHERE (hp.ppDigit LIKE ?) AND (hp.ppString LIKE ?) AND (lp.pSerial = hp.pSerial) ORDER BY lp.pMaker, lp.pName" ';
$con->db_query($qry);

$qry = 'SET @1 = "%200%", @2 = "%dt%"';
$con->db_query($qry);

$qry = 'EXECUTE statement USING @1, @2';
**/

//$keywords[] = "'200d', 20, 'dt'";

$keywords = array('200','dt');
$over3 	   = false;
$mysqli    = new mysqli('localhost', 'root', 'apmsetup', 'oop');

foreach($keywords as $str){
	if(!$over3)
	{ 					// 3글자 이상 검색어가 최소 한개 이상 포함 여부 검증
		if(strlen($str) > 2 ) 
		{
		   $over3 = TRUE;
		}	
	}
	
	if(is_null($over3)) 
	{
		echo '검색어는 3글자 이상 단어를 최소 하나 이상 포함 해야 합니다.';
		return;  
	}
 
 
	$pattern_string = "/[^A-Za-z]/";		 
	$charOnly = preg_replace($pattern_string, "", $str);

 
	if(is_numeric($str))    // ctype_digit($str)
	{
		$front_keys[]   = " (hp.ppDigit LIKE ?) ";		      //  숫자열 전용
		$front_values[] = trim($str);
	}
	else if(ctype_alpha($str) && strlen($charOnly) === strlen($str))    
	{
		$middle_keys[]     = " (hp.ppString LIKE ?) ";     // 문자열 전용
		$middle_values[]   = trim($charOnly);
		$middle_types[]    = 's';
	}
	else                
	{
		$digit  	  = preg_replace("/[^0-9]/", " ", $str);
		$digit   	  = trim($digit);
		$arrDigit  	  = explode(' ', $digit);			
		
		$front_keys[]   = " (hp.ppDigit LIKE ?) ";	         //  숫자열이 빠르므로 첫 숫자열 분리
		$front_values[] = trim($arrDigit[0]);
		
		$back_keys[]    = " (hp.ppName LIKE ?) ";             // 숫자 문자 혼용
		$back_values[]  = trim($str);
		$back_types[]   = 's';
	}
}          // END OF foreach





if(isset($front_keys)) 
{
	$front_values   = array_unique($front_values);
	$front_keys     = array_unique($front_keys);
	
	$values[]       = join(',', $front_values);	
	
	for($i=0, $len = sizeof($front_values); $i < $len; $i++)
	{
		$whereClause[] = join(' AND ', $front_keys); // 숫자검색을 쿼리의 앞에 위치시켜 검색속도 개선			
		$types[]       = 's';		// %%, LIKE로 질의 하면 s로지정해야
	}
	
}	

if(isset($middle_keys))
{
	$whereClause[] = join(' AND ', $middle_keys); 	
	$values[]      = join(',', $middle_values);	
	$types[]       = join('', $middle_types);
}
	
if(isset($back_keys))
{
    $whereClause[] = join(' AND ', $back_keys);	  
	$values[]      = join(',', $back_values);	
	$types[]       = join('', $back_types); 
}	

$whereClause = join(' AND ', $whereClause);

echo $types       = join('', $types);
$values      = join(',', $values);
$values      = explode(',', $values);



echo var_dump($values)."<br />";
echo "[".$values[0]."]<br />";
echo "[".$values[1]."]<br />";

/*
$str = '';
for($i=0, $len = sizeof($values); $i<$len; $i++)
{
	$str .=  '$val'."$i,";
}

echo $str = substr($str, 0, -1);
echo $str = "'$types',".$str;
*/

$str = array();
for($i=0, $len = sizeof($values); $i<$len; $i++)
{
	$str[] =  '$val'."[{$i}]";
}

$str = join(',',$str);

$str = $types.",".$str;
$kk = explode(',', $str);

// var_dump($kk);

$fields = 'lp.pSerial, lp.pType, lp.pMaker, lp.pName, lp.pComment';
$order  = 'lp.pMaker, lp.pName ';
$qry 	= "SELECT $fields FROM list_printer AS lp, haystack_printer AS hp "
		  ."WHERE $whereClause AND (lp.pSerial = hp.pSerial) "
		  ."ORDER BY $order";
		  
echo "<br /><br />".$qry."<br /><br />";

  
$stmt   = $mysqli->prepare($qry);
$stmt->bind_param($types, $val[0],$val[1]);
 
// $stmt->bind_param($str);
//   $stmt->bind_param($types, $str);
// $stmt->bind_param($param);   // $str이 배열인경우 처리 방법

// call_user_func_array(array($stmt, 'bind_param'), $kk);


/*
function refValues($arr)
{
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}


call_user_func_array(array($stmt, 'bind_param'), refValues($kk));
*/

$val[0] = "%30%";
$val[1] = "%dt%";



$stmt->execute();

$stmt->bind_result($pSerial, $pType, $pMaker, $pName, $pComment);
 
while($stmt->fetch())
{
	printf("%s %s %s %s %s<br />", $pSerial, $pType, $pMaker, $pName, $pComment);
}

$stmt->close();
$mysqli->close();





/*



$result = $con->db_query($qry);

$con->db_query('DEALLOCATE PREPARE statement');   // 메모리 비우기

// if($result) echo mysqli_num_rows($result);

/*
$data = null;
	  $pMkr = $k = $data = null;     // 변수 선언 및 초기화

      while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
	  {
	   
	     if($pMkr != $row['pMaker'])
		 {
		    $k    .= "<h4>".$row['pMaker']."</h4>";
		    $pMkr =  $row['pMaker'];
		 }	  
		 
         $k .= "<li>".$row['pSerial']." - ".$row['pName'];
		 
		 $comment =  $row['pComment'];
		 if(isset($comment))
		 {
		    $k .= "<br />".$comment;
		 }
		  $k .= "</li>";  
		  
	  }
	  
	  if(!empty($k))
	  {
	     $data .= "<ul>".$k."</ul>";
	  }
	  
	  echo $data;

*/

?>